package com.ztesoft.zcm.cmdb.util;

import com.ztesoft.zsmart.core.exception.BaseAppException;
import com.ztesoft.zsmart.core.log.ZSmartLogger;
import com.ztesoft.zsmart.zcm.core.exception.ExceptionPublisher;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.ss.formula.eval.NotImplementedException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CellValue;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;

import java.text.DateFormat;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

/**
 * Description: <br>
 *
 * @author yan.qicui<br>
 * #date 2018年07月08日<br>
 * @since V1.0<br>
 */
public class CommonExcelHelper {
    /** 日志 */
    private static final ZSmartLogger logger = ZSmartLogger.getLogger(CommonExcelHelper.class);

    private static final DecimalFormat df = new DecimalFormat("0");

    /**
     * 获取开始解析的坐标
     *
     * @param sheet <br>
     */
    public static int[] getStartCoordinate(Sheet sheet, String startKey) throws BaseAppException {
        logger.debug("sheet name is {0}", sheet.getSheetName());

        int firstRowNum = sheet.getFirstRowNum();
        int lastRowNum = sheet.getLastRowNum();

        for (int i = firstRowNum; i <= lastRowNum; i++) {
            Row row = sheet.getRow(i);
            if (null == row) {
                logger.warn("row is null, row number is: {0}", i);
                continue;
            }
            short lastCellNum = row.getLastCellNum();
            short firstCellNum = row.getFirstCellNum();

            for (short j = firstCellNum; j < lastCellNum; j++) {
                Cell cell = row.getCell(j);
                String cellStr = getCellString(cell);
                if (startKey.toLowerCase().equals(cellStr.toLowerCase())) {
                    return new int[] {
                            i, j
                    };
                }
            }
        }
        ExceptionPublisher.publish("ZCM-00000", "can not find first point, sheet name is sheet.getSheetName()");
        return new int[0];
    }

    /**
     * Description: 获取excel单元格的值<br>
     *
     * @param cell <br>
     * @return <br>
     */
    public static String getCellString(Cell cell) throws BaseAppException {
        try {
            if (cell == null) {
                return "";
            }
            else {
                int cellType = cell.getCellType();
                if (cellType == Cell.CELL_TYPE_STRING || cellType == Cell.CELL_TYPE_BLANK) {
                    return trimStr(cell.getStringCellValue());
                }
                else if (cellType == Cell.CELL_TYPE_NUMERIC) {
                    String str = "";
                    if (DateUtil.isCellDateFormatted(cell)) {
                        Date date = cell.getDateCellValue();
                        DateFormat formater = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
                        str = formater.format(date);
                    }
                    else if (String.valueOf(cell.getNumericCellValue()).contains(".")) {
                        str = df.format(cell.getNumericCellValue()).trim();
                        if (str.endsWith(".0")) {
                            str = str.substring(0, str.lastIndexOf("."));
                        }
                    }
                    return trimStr(str);
                }
                else if (cellType == Cell.CELL_TYPE_BOOLEAN) {
                    return trimStr(String.valueOf(cell.getBooleanCellValue()));
                }
                else if (cellType == Cell.CELL_TYPE_ERROR) {
                    return trimStr(String.valueOf(cell.getErrorCellValue()));
                }
                else if (cellType == Cell.CELL_TYPE_FORMULA) {
                    FormulaEvaluator evaluator = cell.getSheet().getWorkbook().getCreationHelper().createFormulaEvaluator();
                    CellValue tempCellValue = null;
                    try {
                        tempCellValue = evaluator.evaluate(cell);
                    }
                    catch (NotImplementedException e) {
                        try {
                            return String.valueOf(cell.getNumericCellValue());
                        }
                        catch (IllegalStateException e2) {
                            return String.valueOf(cell.getRichStringCellValue());
                        }
                    }
                    int tmpCellType = tempCellValue.getCellType();
                    if (tmpCellType == Cell.CELL_TYPE_STRING || tmpCellType == Cell.CELL_TYPE_BLANK) {
                        return trimStr(tempCellValue.getStringValue());
                    }
                    else if (tmpCellType == Cell.CELL_TYPE_NUMERIC) {
                        String str = String.valueOf(tempCellValue.getNumberValue()).trim();
                        if (str.endsWith(".0")) {
                            str = str.substring(0, str.lastIndexOf("."));
                        }
                        return trimStr(str);
                    }
                    else if (tmpCellType == Cell.CELL_TYPE_BOOLEAN) {
                        return trimStr(String.valueOf(tempCellValue.getBooleanValue()));
                    }
                    else if (tmpCellType == Cell.CELL_TYPE_ERROR) {
                        return trimStr(String.valueOf(tempCellValue.getErrorValue()));
                    }
                }
            }
            return trimStr(cell.getStringCellValue());
        }
        catch (Exception e) {
            logger.error(e);
            ExceptionPublisher.publish("ZCM-00000", "read excel exception", e);
        }
        return "";
    }

    /**
     * 去除前后空格
     *
     * @param str <br>
     * @return <br>
     */
    public static String trimStr(String str) {
        if (null == str) {
            return "";
        }
        str = str.trim();
        return str;
    }

    /**
     * 是否在合并单元格的范围内
     *
     * @param sheet  <br>
     * @param row    <br>
     * @param column <br>
     * @return <br>
     */
    public static boolean isInMergedRegion(Sheet sheet, int row, int column) {
        int sheetMergeCount = sheet.getNumMergedRegions();
        for (int i = 0; i < sheetMergeCount; i++) {
            CellRangeAddress range = sheet.getMergedRegion(i);
            int firstColumn = range.getFirstColumn();
            int lastColumn = range.getLastColumn();
            int firstRow = range.getFirstRow();
            int lastRow = range.getLastRow();
            if (row >= firstRow && row <= lastRow) {
                if (column >= firstColumn && column <= lastColumn) {
                    return true;
                }
            }
        }
        return false;
    }

    private void printCollection(List list) {
        for (Object o : list) {
            logger.debug(o.toString());
        }
    }

    /**
     * 构建单元格样式
     *
     * @param workbook <br>
     * @return <br>
     */
    static CellStyle buildDefaultCellType(Workbook workbook) {
        CellStyle cellStyle = workbook.createCellStyle();
        Font font = workbook.createFont();
        font.setFontName("宋体");
        //字体大小
        font.setFontHeightInPoints((short) 10);
        cellStyle.setFont(font);

        cellStyle.setBorderBottom((short) 1);
        cellStyle.setBorderLeft((short) 1);
        cellStyle.setBorderRight((short) 1);
        cellStyle.setBorderTop((short) 1);
        cellStyle.setAlignment(CellStyle.ALIGN_LEFT);
        return cellStyle;
    }

    /**
     * 获取合并单元格的值
     *
     * @param sheet  <br>
     * @param row    <br>
     * @param column <br>
     * @return <br>
     */
    public static String getMergedRegionValue(Sheet sheet, int row, int column) throws BaseAppException {
        String cellString = CommonExcelHelper.getCellString(sheet.getRow(row).getCell(column));
        if (StringUtils.isNotEmpty(cellString)) {
            return cellString;
        }
        int sheetMergeCount = sheet.getNumMergedRegions();
        for (int i = 0; i < sheetMergeCount; i++) {
            CellRangeAddress ca = sheet.getMergedRegion(i);
            int firstColumn = ca.getFirstColumn();
            int lastColumn = ca.getLastColumn();
            int firstRow = ca.getFirstRow();
            int lastRow = ca.getLastRow();

            if (row >= firstRow && row <= lastRow) {
                if (column >= firstColumn && column <= lastColumn) {
                    Row fRow = sheet.getRow(firstRow);
                    Cell fCell = fRow.getCell(firstColumn);
                    return getCellString(fCell);
                }
            }
        }
        return null;
    }

    /**
     * 如果excel是wps格式，获取合并单元格的cell时，cell会是null，此时不能用该方法<br>
     * 请用getMergedRegionValue(Sheet sheet, int row, int column)
     *
     * @param sheet <br>
     * @param cell  <br>
     * @return <br>
     */
    public static String getMergedRegionValue(Sheet sheet, Cell cell) throws BaseAppException {
        return getMergedRegionValue(sheet, cell.getRowIndex(), cell.getColumnIndex());
    }

}